﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using HRProject.Code.DTO.Salary.Sale;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Web.UI.MobileControls;

namespace HRProject.Code.DAO.Salary.Sale
{
    public class MarketDAO : MySqlDataSource
    {
        private string query = "select c.customer_id as org_id, e.extra_id as market_id, "
            + " c.organization as org_name, substring_index(e.extra_data,'|',-1) as market_name "
            + " from extra_info e, customer c "
            + " where e.item = c.customer_id "
            + " and c.delete_flag<>1 and e.delete_flag<>1 "
            + " and (lower(c.organization) like concat('%',lower(?org_name),'%') "
            + " or lower(substring_index(e.extra_data,'|',-1)) like concat('%',lower(?market_name),'%') )";

        public List<BLSuperMarket> Search(BLSuperMarket filter)
        {
            List<MySqlParameter> queryParams = new List<MySqlParameter>();
            MySqlParameter param1 = new MySqlParameter("?org_name", filter.OrgName);
            MySqlParameter param2 = new MySqlParameter("?market_name", filter.MarketName);
            queryParams.Add(param1);
            queryParams.Add(param2);
            DataTable table = ExecuteQuery(query, queryParams, true);
            List<BLSuperMarket> result = new List<BLSuperMarket>();
            foreach(DataRow row in table.Rows)
            {
                BLSuperMarket market = new BLSuperMarket();
                market.OrgId = Convert.ToInt32(row["org_id"]);
                market.MarketId = Convert.ToInt32(row["market_id"]);
                market.OrgName = (string)row["org_name"];
                market.MarketName = (string)row["market_name"];
                result.Add(market);
            }
            return result;
        }
    }
}
